﻿using SAD_Assignment1.DTO;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace SAD_Assignment1.DAO
{
    public class CommentDAO
    {
        public List<CommentDTO> getComment(int photoID)
        {
            List<CommentDTO> list = new List<CommentDTO>();
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT * FROM getComment(@photoID) ORDER BY post_date DESC";
                    cmd.Parameters.AddWithValue("@photoID", photoID);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable();
                    table.Load(reader);
                    foreach (DataRow row in table.Rows)
                    {
                        int ID = (int)row["ID"];
                        int accID = (int)row["acc_ID"];
                        string body = row["body"].ToString().Replace(Environment.NewLine, "<Br/>");
                        DateTime postDate = DateTime.Parse(row["post_date"].ToString());                        
                        string accName = row["name"].ToString();
                        CommentDTO cmt = new CommentDTO(ID,accID,postDate,body,accName,false);
                        list.Add(cmt);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }

        public List<CommentDTO> getReply(int cmtID)
        {
            List<CommentDTO> list = new List<CommentDTO>();
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT * FROM getReply(@cmtID) ORDER BY post_date ASC";
                    cmd.Parameters.AddWithValue("@cmtID", cmtID);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable();
                    table.Load(reader);
                    foreach (DataRow row in table.Rows)
                    {
                        int ID = (int)row["ID"];
                        int accID = (int)row["acc_ID"];
                        string body = row["body"].ToString().Replace(Environment.NewLine,"<Br/>");
                        DateTime postDate = DateTime.Parse(row["post_date"].ToString());
                        string accName = row["name"].ToString();
                        CommentDTO cmt = new CommentDTO(ID, accID, postDate, body, accName,true);
                        list.Add(cmt);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }

        public bool addComment(int photoID, string body, string email)
        {            
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "addComment";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@photoID", photoID);
                    cmd.Parameters.AddWithValue("@email", email);
                    cmd.Parameters.AddWithValue("@body", body);
                    SqlParameter res = new SqlParameter();
                    res.ParameterName = "@result";
                    res.SqlDbType = System.Data.SqlDbType.Bit;
                    res.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(res);
                    cmd.ExecuteNonQuery();
                    result = (bool)res.Value;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;

        }

        public bool DeleteComment(int cmtID)
        {
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "deleteComment";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@cmtID", cmtID);                    
                    SqlParameter res = new SqlParameter();
                    res.ParameterName = "@result";
                    res.SqlDbType = System.Data.SqlDbType.Bit;
                    res.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(res);
                    cmd.ExecuteNonQuery();
                    result = (bool)res.Value;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;

        }

        public bool AddReply(int cmtID, string body, string email)
        {
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "addReply";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@cmtID", cmtID);
                    cmd.Parameters.AddWithValue("@email", email);
                    cmd.Parameters.AddWithValue("@body", body);
                    SqlParameter res = new SqlParameter();
                    res.ParameterName = "@result";
                    res.SqlDbType = System.Data.SqlDbType.Bit;
                    res.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(res);
                    cmd.ExecuteNonQuery();
                    result = (bool)res.Value;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;

        }

        public bool DeleteReply(int replyID)
        {
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "deleteReply";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@replyID", replyID);
                    SqlParameter res = new SqlParameter();
                    res.ParameterName = "@result";
                    res.SqlDbType = System.Data.SqlDbType.Bit;
                    res.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(res);
                    cmd.ExecuteNonQuery();
                    result = (bool)res.Value;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;

        }

    }
}